#!/usr/bin/env python
# -*- coding:utf-8 -*-

import sqlite3
import os

DB_FILE_PATH = os.path.dirname(os.path.realpath(__file__)) + "\\testDB.db"
TABLE_NAME = "info"
SHOW_SQL = True


def get_conn(path):
    conn = sqlite3.connect(path)
    if os.path.exists(path) and os.path.isfile(path):
        return conn
    else:
        conn = None
        return sqlite3.connect(':memory')


def get_cursor(conn):
    if conn is not None:
        return conn.cursor()
    else:
        return get_conn('').cursor()


# def drop_table(conn, table):
#     '''如果表存在,则删除表，如果表中存在数据的时候，使用该
#     方法的时候要慎用！'''
#     if table is not None and table != '':
#         sql = 'DROP TABLE IF EXISTS ' + table
#         if SHOW_SQL:
#             print('执行sql:[{}]'.format(sql))
#         cu = get_cursor(conn)
#         cu.execute(sql)
#         conn.commit()
#         print('删除数据库表[{}]成功!'.format(table))
#         close_all(conn, cu)
#     else:
#         print('the [{}] is empty or equal None!'.format(sql))


def create_table(conn, sql):
    '''创建数据库表'''
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        cu.execute(sql)
        conn.commit()
        close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def close_all(conn, cu):
    '''关闭数据库游标对象和数据库连接对象'''
    try:
        if cu is not None:
            cu.close()
    finally:
        if cu is not None:
            cu.close()


def save(conn, sql, data):
    '''插入数据'''
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}],参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def fetchall(conn, sql):
    '''查询所有数据'''
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        cu.execute(sql)
        r = cu.fetchall()
        return r
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def fetchone(conn, sql, data):
    '''查询一条数据'''
    if sql is not None and sql != '':
        if data is not None:
            # Do this instead
            d = (data,)
            cu = get_cursor(conn)
            if SHOW_SQL:
                print('执行sql:[{}],参数:[{}]'.format(sql, data))
            cu.execute(sql, d)
            r = cu.fetchall()
            return r
        else:
            print('the [{}] equal None!'.format(data))
    else:
        print('the [{}] is empty or equal None!'.format(sql))
    return None


def update(conn, sql, data):
    '''更新数据'''
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}],参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def delete(conn, sql, data):
    '''删除数据'''
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}],参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


###测试代码demo

#
# def drop_table_test():
#     '''删除数据库表测试'''
#     print('删除数据库表测试...')
#     conn = get_conn(DB_FILE_PATH)
#     drop_table(conn, TABLE_NAME)


def create_table_test():
    '''创建数据库表测试'''
    print('创建数据库表测试...')
    create_table_sql = '''CREATE TABLE `info` (
                          `uuid` varchar(36) default null,
                          `title` TEXT default NULL,
                          `mag_link` TEXT DEFAULT NULL,
                          `result` int(1) DEFAULT NULL
                        )'''
    conn = get_conn(DB_FILE_PATH)
    create_table(conn, create_table_sql)


def save_test(uid, title, mag_link):
    '''保存数据测试...'''
    print('保存数据测试...')
    save_sql = '''INSERT INTO info(uuid,title,mag_link,result) values (?,?,?,?)'''
    data = [(uid, title, mag_link, 0)]
    conn = get_conn(DB_FILE_PATH)
    save(conn, save_sql, data)


def fetchall_test():
    '''查询所有数据...'''
    print('查询所有数据...')
    fetchall_sql = '''SELECT * FROM info'''
    conn = get_conn(DB_FILE_PATH)
    return fetchall(conn, fetchall_sql)


def fetchone_test(uid):
    '''查询一条数据...'''
    print('查询一条数据...')
    fetchone_sql = 'SELECT * FROM info WHERE uuid = ? '
    data = uid
    conn = get_conn(DB_FILE_PATH)
    return fetchone(conn, fetchone_sql, data)


def update_test(uid, result):
    '''更新数据...'''
    print('更新数据...')
    update_sql = 'UPDATE info SET result = ? WHERE uuid = ? '
    data = [(result, uid)]
    conn = get_conn(DB_FILE_PATH)
    update(conn, update_sql, data)


def delete_test():
    '''删除数据...'''
    print('删除数据...')
    delete_sql = 'DELETE FROM student WHERE NAME = ? AND ID = ? '
    data = [('HongtenAA', 1),
            ('HongtenCC', 3)]
    conn = get_conn(DB_FILE_PATH)
    delete(conn, delete_sql, data)


# drop_table_test()
# create_table_test()
# save_test("xxxxxxxxx","test","mag_test")
# print fetchall_test()[0]
# print fetchone_test("xxxxxxxxx")

if __name__ == "__main__":
    print os.path.dirname(os.path.realpath(__file__))
